﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace FeedData
{
    public class FeedDataBO
    {
        public void FeedNhanSu()
        {
            string connect = ConfigurationManager.ConnectionStrings["QLHDConn"].ConnectionString;

            string arr = "Phan Anh Sơn,Adtech,Test,Test,Trưởng bộ phận|"
                        + "Chu Văn Châu,Adtech,Test,Test,Chuyên viên|"
                        + "Nguyễn Quốc Hưng - Adnetwork,Adnetwork,Ballon &amp; Box App,Sản phẩm Box apps,Sales (Executive)|"
                        + "Nguyễn Khắc Tuấn,Adnetwork,Ballon , Box App,Sản phẩm Box apps|"
                        + "Sales (Executive),Đào Thị Thúy Hằng,Support,Đăng tin,Test,Copy writer|"
                        + "Trịnh Huyền Trang,Trợ lý - Giám sát,NCTT Kinh doanh &amp; HTQT,Test,Chuyên viên HTQT|"
                        + "Trần Thu Phương,Sale_kenh,Sale_Dantri,Test,Sales (Executive)|"
                        + "Nguyễn Duy Hưng - Sp Mobile,Khối sản phẩm,Mobile Ad,Sản phẩm Mobile Ad,Hỗ trợ|"
                        + "Hà Đông Tùng,Khối sản phẩm,Mobile Ad,Sản phẩm Mobile Ad,Hỗ trợ|"
                        + "Phan Quốc Chung,Adtech,System Administrator,Test,Hỗ trợ|"
                        + "Hoàng Xuân Thanh,Adtech,Front - end,SAP Front - End,Hỗ trợ|"
                        + "Võ Thị Thu Tâm,Admarket,Admarket-HCM,Test,Sales (Executive)|"
                        + "Lê Trọng Hải,Adtech,Thiết kế Adtech,Test,Thiết kế|"
                        + "Lê Hoàng Nam,Adbooking HCM,Tổng hợp HCM,Test,Sales (Executive)|"
                        + "Lưu Hương Giang,Trợ lý - Giám sát,Đối tác,Test,Chuyên viên phụ trách đối tác";

            string insertSql = "INSERT INTO NhanSu(TenNhanSu, TenPhong,TenBoPhan,TenNhom,ChucDanh,CreatedBy,CreatedAt,RecordStatus,DeletedStatus)"
                            + "             VALUES(@TenNhanSu,@TenPhong,@TenBoPhan,@TenNhom,@ChucDanh,@CreatedBy,@CreatedAt,@RecordStatus,@DeletedStatus)";
            using (SqlConnection myConnection = new SqlConnection(connect))
            {
                myConnection.Open();

                string[] arrData = arr.Split('|');
                for (int i = 0; i < arrData.Length; i++)
                {
                    string[] subData = arrData[i].Split(',');
                    SqlCommand myCommand = new SqlCommand(insertSql, myConnection);
                    myCommand.Parameters.AddWithValue("@TenNhanSu", subData[0]);
                    myCommand.Parameters.AddWithValue("@TenPhong", subData[1]);
                    myCommand.Parameters.AddWithValue("@TenBoPhan", subData[2]);
                    myCommand.Parameters.AddWithValue("@TenNhom", subData[3]);
                    myCommand.Parameters.AddWithValue("@ChucDanh", subData[4]);
                    myCommand.Parameters.AddWithValue("@CreatedBy", "admin");
                    myCommand.Parameters.AddWithValue("@CreatedAt", DateTime.Now);
                    myCommand.Parameters.AddWithValue("@RecordStatus", 2);
                    myCommand.Parameters.AddWithValue("@DeletedStatus", 2);
                    myCommand.ExecuteNonQuery();


                }
                myConnection.Close();

            }
        }

        public void FeedKhachHang()
        {
            string connect = ConfigurationManager.ConnectionStrings["QLHDConn"].ConnectionString;

            string arr = "DN006997,Công ty TNHH Bảo hiểm nhân thọ Great Eastern, 0102631572|"
                        + "DN006996,Công ty TNHH Việt Arredo,0105381232|"
                        + "CN006995,Hoàng Lâm Quyền,186047649|"
                        + "DN006994,Công ty CP Chứng khoán MB,0106393583|"
                        + "CN006993,Trần Thị Yến Nhi,02416470|"
                        + "CN006992,Nguyễn Lưu Chương,014582379|"
                        + "N006991,Bùi Văn Sơn,112173782"
                        + "CN006990,Nguyễn Văn Mạnh,121674755|"
                        + "DN006989,Ngân hàng TMCP Ngoại thương Việt Nam,0100112437|"
                        + "DN006988,Công ty CP Đầu tư Hùng Vương,0101117565|"
                        + "DN006986,Công ty CP F9 Entertainment,0105529601|"
                        + "N006984,Nguyễn Tiến Đạt,012073065 |"
                        + "CN006983,Đặng Trung Trí,121674747|"
                        + "DN006982,Công ty CP Thương mại Hưng Thuận Thành,0105758217";


            string insertSql = "INSERT INTO KhachHang(MaKhachHang, TenKhachHang,MaSoThue,CreatedBy,CreatedAt,RecordStatus,DeletedStatus)"
                            + "             VALUES(@MaKhachHang,@TenKhachHang,@MaSoThue,@CreatedBy,@CreatedAt,@RecordStatus,@DeletedStatus)";
            using (SqlConnection myConnection = new SqlConnection(connect))
            {
                myConnection.Open();

                string[] arrData = arr.Split('|');
                for (int i = 0; i < arrData.Length; i++)
                {
                    string[] subData = arrData[i].Split(',');
                    SqlCommand myCommand = new SqlCommand(insertSql, myConnection);
                    myCommand.Parameters.AddWithValue("@TenKhachHang", subData[1]);
                    myCommand.Parameters.AddWithValue("@MaKhachHang", subData[0]);
                    myCommand.Parameters.AddWithValue("@MaSoThue", subData[2]);
                    myCommand.Parameters.AddWithValue("@CreatedBy", "admin");
                    myCommand.Parameters.AddWithValue("@CreatedAt", DateTime.Now);
                    myCommand.Parameters.AddWithValue("@RecordStatus", 2);
                    myCommand.Parameters.AddWithValue("@DeletedStatus", 2);
                    myCommand.ExecuteNonQuery();


                }
                myConnection.Close();

            }
        }

        public void FeedDanhMucHopDong()
        {
            string connect = ConfigurationManager.ConnectionStrings["QLHDConn"].ConnectionString;

            string arr = "214,BT,137,CPC|138,CPM|343,DF|58,DT|286,GG|136,HT|135,K|175,Khac|277,MKP|278,MKP_CM4|310,NB|345,NBDT|134,PC|54,QC|533,SH|211,TT";


            string insertSql = "INSERT INTO DanhMucHopDong(TenHopDong, CreatedBy,CreatedAt,RecordStatus,DeletedStatus)"
                            + "                     VALUES(@TenHopDong,@CreatedBy,@CreatedAt,@RecordStatus,@DeletedStatus)";
            using (SqlConnection myConnection = new SqlConnection(connect))
            {
                myConnection.Open();

                string[] arrData = arr.Split('|');
                for (int i = 0; i < arrData.Length; i++)
                {
                    string[] subData = arrData[i].Split(',');
                    SqlCommand myCommand = new SqlCommand(insertSql, myConnection);
                    myCommand.Parameters.AddWithValue("@TenHopDong", subData[1]);
                    myCommand.Parameters.AddWithValue("@CreatedBy", "admin");
                    myCommand.Parameters.AddWithValue("@CreatedAt", DateTime.Now);
                    myCommand.Parameters.AddWithValue("@RecordStatus", 2);
                    myCommand.Parameters.AddWithValue("@DeletedStatus", 2);
                    myCommand.ExecuteNonQuery();


                }
                myConnection.Close();

            }
        }

        public void FeedHinhThucQuangCao()
        {
            string connect = ConfigurationManager.ConnectionStrings["QLHDConn"].ConnectionString;

            string arr = "15, Ad-Page |12, Bảo trợ thông tin |14, Chi phí khác |22, Content Marketing |21, CPA |7, CPC |5, CPD |6, CPM |11, Giao lưu trực tuyến |13, Mua ngoài |10, PR |9, Sản phẩm TMDT |26, Self-serving |16, Sponsor-Post";


            string insertSql = "INSERT INTO HinhThucQuangCao(HinhThucQuangCaoID,TenHinhThucQuangCao, CreatedBy,CreatedAt)"
                            + "                     VALUES(@HinhThucQuangCaoID,@TenHinhThucQuangCao,@CreatedBy,@CreatedAt)";
            using (SqlConnection myConnection = new SqlConnection(connect))
            {
                myConnection.Open();

                string[] arrData = arr.Split('|');
                for (int i = 0; i < arrData.Length; i++)
                {
                    string[] subData = arrData[i].Split(',');
                    SqlCommand myCommand = new SqlCommand(insertSql, myConnection);

                    myCommand.Parameters.AddWithValue("@HinhThucQuangCaoID", Convert.ToInt64(subData[0].Trim()));
                    myCommand.Parameters.AddWithValue("@TenHinhThucQuangCao", subData[1].Trim());

                    myCommand.Parameters.AddWithValue("@CreatedBy", "admin");
                    myCommand.Parameters.AddWithValue("@CreatedAt", DateTime.Now);
                    myCommand.Parameters.AddWithValue("@RecordStatus", 2);
                    myCommand.Parameters.AddWithValue("@DeletedStatus", 2);
                    myCommand.ExecuteNonQuery();


                }
                myConnection.Close();

            }
        }


        public void FeedSanPham()
        {
            string connect = ConfigurationManager.ConnectionStrings["QLHDConn"].ConnectionString;

            string arr = "305, Adpage |585, AdX |339, Balloon Ads |140, Banner CPD |549, Banner CPD chuyên trang |245, Bảo trợ thông tin |370, Box App |564, Box App Multi Branding |385, Box giá vàng |264, Box nổi bật |300, Box sản phẩm Hot |252, Chi phí hosting |253, Chi phí khác |535, Chi phí quản lý |560, Chi phí sản xuất |251, Chi phí thiết kế |420, CPA |144, CPC Admarket |299, CPC Plus Admarket |337, CPM Admarket |231, CPM Chuyên trang |238, CPM Mass |531, CPM Multi |141, Đăng tin |306, Facebook Ads |250, Giao lưu trực tuyến |423, Google Ads |242, Lượt up |342, Mobile |247, Mua ngoài |381, Sponsored Post |271, Tài trợ |243, Tin nổi bật |241, Tin vip |240, TVC Online";


            string insertSql = "INSERT INTO SanPham(SanPhamID,TenSanPham, CreatedBy,CreatedAt)"
                            + "                     VALUES(@SanPhamID,@TenSanPham,@CreatedBy,@CreatedAt)";
            using (SqlConnection myConnection = new SqlConnection(connect))
            {
                myConnection.Open();

                string[] arrData = arr.Split('|');
                for (int i = 0; i < arrData.Length; i++)
                {
                    string[] subData = arrData[i].Split(',');

                    SqlCommand myCommand = new SqlCommand(insertSql, myConnection);
                    myCommand.Parameters.AddWithValue("@SanPhamID", Convert.ToInt64(subData[0].Trim()));
                    myCommand.Parameters.AddWithValue("@TenSanPham", subData[1].Trim());
                    myCommand.Parameters.AddWithValue("@CreatedBy", "admin");
                    myCommand.Parameters.AddWithValue("@CreatedAt", DateTime.Now);
                    myCommand.ExecuteNonQuery();


                }
                myConnection.Close();

            }
        }

        public void FeedNhanHang()
        {
            string connect = ConfigurationManager.ConnectionStrings["QLHDConn"].ConnectionString;

            string arr = "Bếp Huy Hoàng,Samsung Air Conditioner,dealgiadung.com,Máy tính TS102,thanhsonhnvn,Solar BK,WB Capital,Tiến Phát,Panasonic, Toshiba,Panasonic, Canon,Hitachi,LG Retainer,Panasonic,Panasonic, Pana Aircon, Electrolux,Samsung,Sanyo,Sharp,HP, ANZ, Sony, FedEx,Heinz, Panasonic, Toshiba, Cannon,Sony, Coca Cola,suzuki, SYM, Sony, Liên Việt Postbank,CPA Australia,Đầu Tư Quốc Tế AGA,Cổ Phần Đầu Tư và Phân Tích Kinh Tế Hà Nội,Thẩm định giá Hoàng Quân,vay24.vn,EastSpring Investment,Mirae Asset,Sacombank Leasing,Safecap,Minh Quân,Bán xe cũ,Mua bán xe máy cũ HN,Shop banhangthailandonline,Xe máy Tuấn Dung,Xe máy Nguyễn Oanh,Minh xe máy,Xe Máy Tuấn Nam,Xe máy Thưởng Oanh,Xe máy Anh Tú,Xe máy Tuấn Anh,Xe máy Chùa Hà,Shop Thanhlymaytinh,Xe máy Anh Thiện,Huệ Mai xe máy,Xe máy Nam Phong,Xe máy Quang Ngọc,Hùng xe máy cũ,Xe máy TranBao,Xe máy Việt Bắc";


            string insertSql = "INSERT INTO NhanHang(TenNhanHang, CreatedBy,CreatedAt)"
                            + "                     VALUES(@TenNhanHang,@CreatedBy,@CreatedAt)";
            using (SqlConnection myConnection = new SqlConnection(connect))
            {
                myConnection.Open();

                string[] arrData = arr.Split(',');
                for (int i = 0; i < arrData.Length; i++)
                {
                    SqlCommand myCommand = new SqlCommand(insertSql, myConnection);
                    myCommand.Parameters.AddWithValue("@TenNhanHang", arrData[i].Trim());
                    myCommand.Parameters.AddWithValue("@CreatedBy", "admin");
                    myCommand.Parameters.AddWithValue("@CreatedAt", DateTime.Now);
                    myCommand.ExecuteNonQuery();


                }
                myConnection.Close();

            }
        }

        public void FeedWebsite()
        {
            string connect = ConfigurationManager.ConnectionStrings["QLHDConn"].ConnectionString;

            string arr = "2dep.net,6giosang.com,Afamily.vn,Alobacsi.vn,Anninhthudo.vn,Aoetv.vn,Arsenal.com.vn,autopro.com.vn,autopro.vn,Autovip.vn,Baodatviet.vn,Baohay.vn,Baomoi.com,Blogviet.com.vn,Bomtan.org,Bongda.com.vn,Bongda24h.vn,Bongdapro.vn,Các site khác,Cafebiz.vn,Cafef.vn,Camnangcuoihoi.com,chaobuoisang.net,Chimsedinang.com,Chogiamgia.net,Cohet.vn,Danong.com,dantri.com.vn,Dddn.com.vn,Diachiso.vn,Diendankienthuc.net,Doanhnhansaigon.vn,Docbao.vn,Duongdenthanhcong.net,Enbac.com,f319.com,Forum.soha.vn,Freely.vn,Freexoso.com,GameK.vn,Gametv.vn,GenK.vn,Giacaphe.com,Giadinh.net.vn,Giaitriviet.net.vn,go.vn,Goccay.vn,guu.vn,Haiduong360.vn,Haivl.com,Hanoimoi.com.vn,Haycuoi.com,Hoahoctro.vn,Hoaphuongdo.vn,Hoclamgiau.vn,ibongda.vn,ihay.vn,Kenh14.vn,Ladies.vn,linkhay.com,loidich.com,Mangthai.vn,maskonline.vn,Mevabe.net,Muare.vn,Muctim.com.vn,Netcodo.vn,news.ladies.vn,ngoisaoonline.vn,nhacvietplus.com.vn,Nhatkybe.vn,Nhipcaudautu.vn,Nhipdapcamxuc.com,nld.com.vn,Nongnghiep.vn,Phim.soha.vn,Phim4d.com,Phimf.com,Phimnoi.com,Phongcachla.vn,Plo.vn,Quangninh24h.vn,Quocphong.vn,Raovat123.com,rolo.vn,Rongbay.com,Saigonnews.vn,sannhac.com,Sanotc.com,Sggp.org.vn,Sgtt.vn,Socbay.com,socnhi.com,socvui.sohagame.vn,Soha.vn,Studentzone.vn,Suckhoedoisong.vn,Tamdiem.net,Tech24.vn,thanhnien.com.vn,Thegioidienanh.vn,Thitruongtaichinh.vn,Thongtintuyensinh.vn,Thuocbietduoc.com.vn,Thvl.vn,Tienphong.vn,Timdiemthi.com,Tinhkinhdoanh.com.vn,Tinhte.vn,tinnong.vn,Tinthethao.com.vn,Tocdo.vn,Tratu.soha.vn,Tructiep.vn,Truyentranhtuan.com,Ttvnol.com,V1vn.com,Vang-24h.com.vn,video.soha.vn,vietnamnet.vn,Viettorrent.vn,Vinacorp.vn,Violet.vn,Vir.com.vn,vneconomy.vn,Vnmedia.vn,Vntech.vn,Vntimes.com.vn,Vn-zoom.com,Vozforums.com,Vtc.vn,Vtv.vn,Vuighe.net,Xahoithongtin.com.vn,Xaluan.com,xinhxinh.com.vn,Xskt.com.vn,Xzone.vn,Yeudulich.vn";


            string insertSql = "INSERT INTO WebSite(TenWebSite, CreatedBy,CreatedAt)"
                            + "                     VALUES(@TenWebSite,@CreatedBy,@CreatedAt)";
            using (SqlConnection myConnection = new SqlConnection(connect))
            {
                myConnection.Open();

                string[] arrData = arr.Split(',');
                for (int i = 0; i < arrData.Length; i++)
                {
                    SqlCommand myCommand = new SqlCommand(insertSql, myConnection);
                    myCommand.Parameters.AddWithValue("@TenWebSite", arrData[i].Trim());
                    myCommand.Parameters.AddWithValue("@CreatedBy", "admin");
                    myCommand.Parameters.AddWithValue("@CreatedAt", DateTime.Now);
                    myCommand.ExecuteNonQuery();


                }
                myConnection.Close();

            }
        }

        public void FeedDonVi()
        {
            string connect = ConfigurationManager.ConnectionStrings["QLHDConn"].ConnectionString;

            string arr = "1 ,  CPM|2 ,  CPC|3 ,  Ngày|4 ,  Tuần|5 ,  Tháng|6 ,  Năm|7 ,  Bài|8 ,  Phiếu|9 ,  Survey|10,  Gói|11,  Clip|12,  đ/v|13,  Tiền|14,  Khác|15,  KM|16,  resize|17,  design|18,  lần|19,  CPA|20,  Diễn đàn|21,  Like";


            string insertSql = "INSERT INTO DonViTinh(DonViTinhID,TenDonViTinh, CreatedBy,CreatedAt)"
                            + "                     VALUES(@DonViTinhID,@TenDonViTinh,@CreatedBy,@CreatedAt)";
            using (SqlConnection myConnection = new SqlConnection(connect))
            {
                myConnection.Open();

                string[] arrData = arr.Split('|');
                for (int i = 0; i < arrData.Length; i++)
                {
                    string[] subData = arrData[i].Split(',');

                    SqlCommand myCommand = new SqlCommand(insertSql, myConnection);
                    myCommand.Parameters.AddWithValue("@DonViTinhID", Convert.ToInt64(subData[0].Trim()));
                    myCommand.Parameters.AddWithValue("@TenDonViTinh", subData[1].Trim());
                    myCommand.Parameters.AddWithValue("@CreatedBy", "admin");
                    myCommand.Parameters.AddWithValue("@CreatedAt", DateTime.Now);
                    myCommand.ExecuteNonQuery();


                }
                myConnection.Close();

            }
        }

        public void FeedChuyenMuc()
        {
            string connect = ConfigurationManager.ConnectionStrings["QLHDConn"].ConnectionString;

            string arr = "2-Tek,Ảnh,Âu - Mỹ,Bản tin 46',Bên lề,Bóng đá,Box Chuyển động doanh nghiệp,Box Công nghệ và phong cách,Box Sự kiện,Box Sự kiện hội thảo,Box Thế giới phẳng,Box Thông tin doanh nghiệp,Cafef Biz,Câu chuyện thật,Châu Á,Chùm ảnh,Chuyên mục,Chuyên mục cao cấp,Chuyên mục cấp 1,Chuyên mục cấp 2,Chuyên trang nhóm 1,Chuyên trang nhóm 2,Cine,Công nghệ vui,Cool,Đẹp +,Địa phương,Đời sống,Du học,Fashion,Fashion Icon,Funny,Giải trí,Giới tính,Góc trái tim,Handmade,Hehe Horo,Học đường,Home Quizz,Horoscope,How to,Khám phá,Kiến thức giới tính,Kitchen,Lạ,Làm đẹp,Loại 1,Loại 2,Loại 3,Love story,Made by me,Made by me + Sàn nhạc,Mẹo vặt,Mốt 14,Mọt Phim,Musik,Nhân đạo,Nhân vật,Nhóm 1,Nhóm 2,Paparazzi,Phim Âu - Mỹ,Phim Châu Á,Phim Việt Nam,Phong cách,Quán xá,Quizz,Quốc tế,Sản phẩm mới,Sao Việt,Sport,Star,Star Style,Style to share,Tâm sự,Thế giới,Thủ thuật,Thư viện,Tin tức,Trắc nghiệm,Trái tim có nắng,Trang chủ,Trang chủ (Loại 1 Avatar x2),Trang chủ (Loại 1),Trang chủ (Loại 2),Trang chủ (loại 2) Nhóm 1,Trang chủ (loại 2) Nhóm 2,Trang chủ (Loại 3),Trang chủ (Loại đặc biệt),Trang chủ loại 1,Trang chuyên mục,Trang chuyên mục (Loại 1),Trang chuyên mục (Loại 2),Trang chuyên mục nhóm 1,Trang tiểu mục,Trang tiểu mục (Loại 1),Trang tiểu mục (Loại 2),Trang tiểu mục nhóm 1,Trang tiểu mục nhóm 2,TROLL SPORT,Trong nước,Tuyensinh.dantri,TV show,Văn hóa - Giải trí,Văn hoá giải trí,Vanlentin 2014,Video,Việt Nam,Xã hội,Xã hội - Quốc tế - Quân sự - Thế giới đó đây,Xem - Ăn - Chơi,Xuyên trang,Yes or No,Blank";


            string insertSql = "INSERT INTO ChuyenMuc(TenChuyenMuc, CreatedBy,CreatedAt)"
                            + "                     VALUES(@TenChuyenMuc,@CreatedBy,@CreatedAt)";
            using (SqlConnection myConnection = new SqlConnection(connect))
            {
                myConnection.Open();

                string[] arrData = arr.Split(',');
                for (int i = 0; i < arrData.Length; i++)
                {
                    SqlCommand myCommand = new SqlCommand(insertSql, myConnection);
                    myCommand.Parameters.AddWithValue("@TenChuyenMuc", arrData[i].Trim());
                    myCommand.Parameters.AddWithValue("@CreatedBy", "admin");
                    myCommand.Parameters.AddWithValue("@CreatedAt", DateTime.Now);
                    myCommand.ExecuteNonQuery();


                }
                myConnection.Close();

            }
        }
        public void FeedBanner()
        {
            string connect = ConfigurationManager.ConnectionStrings["QLHDConn"].ConnectionString;

            string arr = "2,2B,300x250,300x385,300x600,3B,468x90,468x90 300x250,500x300,500x385,980x180,980x90,Article banner,Article Banner A,Article Banner B,Avatar Banner,B,Banner,Banner Play,Banner TVC,Big Balloon/H-big Balloon,Bottom banner,C,CatFish,Center banner,Center banner expand,Customized,Double Right Banner,Double Top Banner,Expandable Banner,Expert,Fiteye Video,High banner,High banner A,High Banner A+B,High Banner B,High Right Banner,Hot Banner,Hot Banner 2A,Hot Banner 2C,Hot Banner 2D,Hot banner A,Hot Banner B,Icon 1,Icon 2,Icon 3,Icon 4,Icon 5,Inline,iPlay Article,iPlay Expand,iTVC Classic,Medium Rectangle,Medium Rectangle 1,Medium Rectangle 2,Medium Rectangle 3,Mini banner,Mini Banner 3A,Mini Banner 3C,Mini banner A,Mini banner B,Mini banner C,Page Take-over,Plus (1 tháng),Plus (3 tháng),Plus (6 tháng),Pop Up,Pre-roll Classic,Pre-roll Plus,Pro (1 tháng),Pro (3 tháng),Pro (6 tháng),Rich media icon,Right Banner 1,Right Banner 2,Right banner A,Right Banner A+B,Sky scraper A,Sky Scraper B,Sky Scrapper A+B,Slider,Sponsored Box,Standard Banner,Start (1 tháng),Start (3 tháng),Start (6 tháng),Super iTVC,Super Pre-roll,Top Banner,Top banner đúp,blank";


            string insertSql = "INSERT INTO Banner(Banner, CreatedBy,CreatedAt)"
                            + "                     VALUES(@Banner,@CreatedBy,@CreatedAt)";
            using (SqlConnection myConnection = new SqlConnection(connect))
            {
                myConnection.Open();

                string[] arrData = arr.Split(',');
                for (int i = 0; i < arrData.Length; i++)
                {
                    SqlCommand myCommand = new SqlCommand(insertSql, myConnection);
                    myCommand.Parameters.AddWithValue("@Banner", arrData[i].Trim());
                    myCommand.Parameters.AddWithValue("@CreatedBy", "admin");
                    myCommand.Parameters.AddWithValue("@CreatedAt", DateTime.Now);
                    myCommand.ExecuteNonQuery();


                }
                myConnection.Close();

            }
        }

        public void FeedTaiKhoanAdmk()
        {
            string connect = ConfigurationManager.ConnectionStrings["QLHDConn"].ConnectionString;

            string arr = "Aaliyah ,Aaron   ,Abagail ,Abbey   ,Abbie   ,Abbigail,Abby    ,Abdul   ,Abdullah,Abel    ,Abigail ,Abigale ,Abigayle,Abraham ,Abram   ,Abril   ,Acacia  ,Ace Boy ,Ada Girl,Adalyn  ,Adalynn ,Adam    ,Adamaris,Adan    ,Addie   ,Addison ,Addyson ,Adelaide,Adele";


            string insertSql = "INSERT INTO TaiKhoanAdmk(TenTaiKhoanAdmk, CreatedBy,CreatedAt)"
                            + "                     VALUES(@TenTaiKhoanAdmk,@CreatedBy,@CreatedAt)";
            using (SqlConnection myConnection = new SqlConnection(connect))
            {
                myConnection.Open();

                string[] arrData = arr.Split(',');
                for (int i = 0; i < arrData.Length; i++)
                {
                    SqlCommand myCommand = new SqlCommand(insertSql, myConnection);
                    myCommand.Parameters.AddWithValue("@TenTaiKhoanAdmk", arrData[i].Trim());
                    myCommand.Parameters.AddWithValue("@CreatedBy", "admin");
                    myCommand.Parameters.AddWithValue("@CreatedAt", DateTime.Now);
                    myCommand.ExecuteNonQuery();


                }
                myConnection.Close();

            }
        }

        public void FeedTag()
        {
            string connect = ConfigurationManager.ConnectionStrings["QLHDConn"].ConnectionString;

            string arr = "Aclass,A-Class,Bất động sản,Blank,Cuộc sống số,Đẹp ++,Game,Gia đình - Phụ nữ,Gia đình - Sức khoẻ,Gia đình - Sức khỏe,Gia đình- Sức khoẻ,Giải trí - Bạn đọc,Giải trí - Giới trẻ,Giải trí - Xã hội,Giáo dục,Giáo dục - Nghề Nghiệp,Kinh doanh,Mega Homepage,Mua sắm tiêu dùng,News,Sức khoẻ,Sức khỏe,Tài chính,Thể thao - Ô tô - Xe máy,Thể thao - Xe ++,Tin tức tổng hợp,Tổng hợp,Xã hội - Pháp luật";


            string insertSql = "INSERT INTO Tag(TenTag)"
                            + "                     VALUES(@TenTag)";
            using (SqlConnection myConnection = new SqlConnection(connect))
            {
                myConnection.Open();

                string[] arrData = arr.Split(',');
                for (int i = 0; i < arrData.Length; i++)
                {
                    SqlCommand myCommand = new SqlCommand(insertSql, myConnection);
                    myCommand.Parameters.AddWithValue("@TenTag", arrData[i].Trim());
                    myCommand.ExecuteNonQuery();


                }
                myConnection.Close();

            }
        }


        public void FeedDonViSanPham()
        {
            string connect = ConfigurationManager.ConnectionStrings["QLHDConn"].ConnectionString;

            string arr = "23 , 3  , 140, 0 |24 , 4  , 140, 1 |25 , 5  , 140, 0 |26 , 6  , 140, 0 |27 , 7  , 141, 1 |28 , 2  , 144, 0 |29 , 1  , 228, 0 |30 , 3  , 228, 0 |31 , 4  , 228, 1 |32 , 5  , 228, 0 |33 , 6  , 228, 0 |34 , 1  , 231, 1 |35 , 1  , 237, 1 |36 , 1  , 238, 1 |37 , 1  , 239, 0 |38 , 1  , 240, 1 |39 , 12 , 241, 0 |40 , 12 , 242, 0 |41 , 12 , 243, 0 |42 , 12 , 244, 0 |43 , 12 , 245, 0 |234, 12 , 535, 1 |45 , 12 , 248, 0 |46 , 12 , 249, 0 |47 , 12 , 250, 0 |48 , 12 , 251, 0 |49 , 12 , 252, 0 |50 , 12 , 253, 0 |51 , 12 , 264, 0 |52 , 12 , 268, 0 |53 , 12 , 269, 1 |54 , 12 , 270, 0 |55 , 12 , 271, 0 |56 , 12 , 284, 0 |57 , 2  , 299, 0 |58 , 12 , 300, 0 |59 , 7  , 253, 0 |60 , 11 , 253, 0 |61 , 10 , 144, 1 |62 , 16 , 253, 0 |63 , 17 , 253, 0 |64 , 18 , 253, 1 |65 , 10 , 245, 1 |66 , 10 , 250, 1 |67 , 18 , 252, 1 |68 , 10 , 284, 1 |235, 12 , 560, 0 |228, 4  , 247, 1 |229, 5  , 247, 0 |72 , 10 , 299, 1 |73 , 16 , 251, 0 |74 , 17 , 251, 1 |75 , 10 , 242, 1 |76 , 10 , 271, 1 |77 , 10 , 228, 0 |78 , 4  , 241, 0 |79 , 5  , 241, 1 |80 , 5  , 248, 1 |81 , 4  , 248, 0 |82 , 10 , 270, 1 |83 , 10 , 249, 1 |84 , 10 , 244, 1 |85 , 5  , 243, 0 |86 , 4  , 243, 1 |87 , 4  , 264, 1 |88 , 5  , 264, 0 |89 , 5  , 300, 0 |90 , 4  , 300, 1 |91 , 10 , 268, 1 |92 , 4  , 253, 0 |93 , 4  , 252, 0 |94 , 3  , 252, 0 |95 , 2  , 305, 1 |96 , 7  , 305, 0 |97 , 4  , 305, 0 |98 , 2  , 306, 1 |99 , 5  , 268, 0 |100, 1  , 337, 1 |101, 1  , 337, 0 |102, 1  , 339, 1 |103, 10 , 239, 1 |104, 10 , 337, 0 |105, 2  , 239, 0 |233, 5  , 535, 0 |107, 10 , 306, 0 |108, 1  , 352, 0 |109, 3  , 352, 0 |110, 7  , 352, 0 |111, 4  , 352, 0 |112, 2  , 284, 0 |113, 10 , 305, 0 |232, 12 , 247, 0 |115, 1  , 370, 1 |116, 1  , 375, 1 |117, 7  , 250, 0 |118, 7  , 271, 0 |119, 2  , 339, 0 |120, 2  , 342, 1 |121, 10 , 342, 0 |122, 1  , 381, 1 |123, 1  , 308, 1 |124, 5  , 385, 1 |125, 4  , 385, 0 |126, 6  , 385, 0 |127, 1  , 311, 1 |128, 10 , 420, 1 |129, 2  , 423, 0 |130, 10 , 240, 0 |131, 10 , 339, 0 |132, 1  , 337, 0 |133, 10 , 381, 0 |134, 19 , 339, 0 |135, 1  , 141, 0 |136, 2  , 437, 1 |137, 10 , 437, 0 |138, 12 , 437, 0 |139, 2  , 381, 0 |140, 1  , 531, 1 |141, 2  , 531, 0 |142, 10 , 531, 0 |143, 7  , 243, 0 |144, 10 , 375, 0 |145, 4  , 535, 0 |146, 7  , 535, 0 |147, 11 , 535, 0 |148, 16 , 535, 0 |149, 17 , 535, 0 |150, 18 , 535, 0 |151, 4  , 536, 0 |152, 7  , 536, 0 |153, 11 , 536, 0 |154, 16 , 536, 0 |155, 17 , 536, 0 |156, 18 , 536, 0 |157, 4  , 537, 0 |158, 7  , 537, 0 |159, 11 , 537, 0 |160, 16 , 537, 0 |161, 17 , 537, 0 |162, 18 , 537, 1 |163, 4  , 538, 0 |164, 7  , 538, 0 |165, 11 , 538, 0 |166, 16 , 538, 0 |167, 17 , 538, 0 |168, 18 , 538, 1 |169, 4  , 539, 0 |170, 7  , 539, 0 |171, 11 , 539, 1 |172, 16 , 539, 0 |173, 17 , 539, 0 |174, 18 , 539, 0 |175, 4  , 540, 0 |176, 7  , 540, 0 |177, 11 , 540, 0 |178, 16 , 540, 0 |179, 17 , 540, 1 |180, 18 , 540, 0 |181, 4  , 541, 1 |182, 7  , 541, 0 |183, 11 , 541, 0 |184, 16 , 541, 0 |185, 17 , 541, 0 |186, 18 , 541, 0 |187, 4  , 542, 0 |188, 7  , 542, 0 |189, 11 , 542, 0 |190, 16 , 542, 0 |191, 17 , 542, 0 |192, 18 , 542, 1 |193, 1  , 543, 1 |194, 2  , 543, 0 |195, 1  , 544, 1 |196, 2  , 544, 0 |197, 1  , 545, 1 |198, 2  , 545, 0 |199, 1  , 546, 1 |200, 2  , 546, 0 |201, 1  , 547, 1 |202, 2  , 547, 0 |203, 1  , 548, 1 |204, 2  , 548, 0 |205, 3  , 549, 0 |206, 4  , 549, 1 |207, 5  , 549, 0 |208, 6  , 549, 0 |209, 2  , 554, 0 |210, 10 , 554, 1 |211, 4  , 560, 0 |212, 7  , 560, 0 |213, 11 , 560, 0 |214, 16 , 560, 0 |215, 17 , 560, 0 |216, 18 , 560, 1 |217, 12 , 144, 0 |218, 10 , 370, 0 |219, 12 , 370, 0 |220, 12 , 539, 0 |221, 12 , 541, 0 |226, 1  , 247, 0 |231, 7  , 247, 0 |227, 3  , 247, 0 |230, 6  , 247, 0 |236, 12 , 540, 0 |237, 12 , 538, 0 |238, 12 , 537, 0 |239, 12 , 542, 0 |240, 5  , 554, 0 |241, 5  , 284, 0 |242, 5  , 437, 0 |243, 5  , 423, 1 |244, 10 , 561, 1 |245, 7  , 563, 0 |246, 10 , 563, 1 |247, 10 , 537, 0 |248, 12 , 563, 0 |249, 18 , 563, 0 |250, 20 , 563, 0 |251, 1  , 562, 0 |252, 2  , 562, 0 |253, 10 , 562, 1 |254, 10 , 550, 1 |255, 10 , 551, 1 |256, 10 , 552, 1 |257, 10 , 553, 1 |258, 2  , 550, 0 |259, 21 , 550, 0 |260, 11 , 557, 1 |261, 11 , 559, 1 |262, 12 , 556, 0 |263, 18 , 556, 1 |264, 18 , 557, 0 |265, 3  , 564, 0 |266, 4  , 564, 1 |267, 5  , 564, 0 |268, 10 , 564, 0 |269, 12 , 564, 0 |270, 1  , 342, 0 |271, 1  , 306, 0 |272, 12 , 557, 0 |273, 12 , 558, 0 |274, 12 , 547, 0 |275, 2  , 238, 0 |276, 10 , 238, 0 |277, 1  , 144, 0 |278, 1  , 370, 1 |279, 3  , 370, 0 |280, 4  , 370, 0 |281, 5  , 370, 0 |282, 6  , 370, 0 |283, 10 , 370, 0 |284, 12 , 370, 0 |285, 10 , 423, 0 |286, 4  , 370, 0 |287, 5  , 370, 0 |288, 6  , 370, 0 |289, 10 , 370, 0 |290, 12 , 370, 0";


            string insertSql = "INSERT INTO DonViSanPham(DonViSanPhamID,DonViTinhREF,SanPhamREF,[Default])"
                            + "                     VALUES(@DonViSanPhamID,@DonViTinhREF,@SanPhamREF,@Default)";
            using (SqlConnection myConnection = new SqlConnection(connect))
            {
                myConnection.Open();

                string[] arrData = arr.Split('|');
                for (int i = 0; i < arrData.Length; i++)
                {
                    string[] subData = arrData[i].Split(',');

                    SqlCommand myCommand = new SqlCommand(insertSql, myConnection);
                    myCommand.Parameters.AddWithValue("@DonViSanPhamID", Convert.ToInt64(subData[0].Trim()));
                    myCommand.Parameters.AddWithValue("@DonViTinhREF", Convert.ToInt32(subData[1].Trim()));
                    myCommand.Parameters.AddWithValue("@SanPhamREF", Convert.ToInt32(subData[2].Trim()));
                    myCommand.Parameters.AddWithValue("@Default", Convert.ToInt32(subData[3].Trim()));
                    myCommand.ExecuteNonQuery();


                }
                myConnection.Close();

            }
        }

        public void FeedExcel()
        {
            string connect = ConfigurationManager.ConnectionStrings["QLHDConn"].ConnectionString;

            String fileName = @"E:\Hung\Projects\A\ADM-Tools\BaoGia20140805.xlsx";
            List<BaoGia> result = ExcelUtils.ExecuteExcel(fileName);
            int count = 1;
            string insertSql = "INSERT INTO BaoGiaHopDong([HinhThucQuangCaoREF]"
                                                        + " ,[SanPhamREF]"
                                                        + " ,[DonViTinhREF]"
                                                        + " ,[TagREF]"
                                                        + " ,[WebsiteREF]"
                                                        + " ,[ChuyenMucREF]"
                                                        + " ,[BannerREF]"
                                                        + " ,[DonGia]"
                                                        + " ,[ThoiGianHieuLucBatDau]"
                                                        + ",[ThoiGianHieuLucKetThuc])"
                                                        + "VALUES(@HinhThucQuangCaoREF"
                                                            + " ,@SanPhamREF"
                                                            + " ,@DonViTinhREF"
                                                            + " ,@TagREF"
                                                            + " ,@WebsiteREF"
                                                            + " ,@ChuyenMucREF"
                                                            + " ,@BannerREF"
                                                            + " ,@DonGia"
                                                            + " ,@ThoiGianHieuLucBatDau"
                                                            + " ,@ThoiGianHieuLucKetThuc)";
            using (SqlConnection myConnection = new SqlConnection(connect))
            {
                myConnection.Open();
                foreach (BaoGia row in result)
                {
                    count++;
                    try
                    {
                        //if (count == 763)
                        {
                            SqlCommand myCommand = new SqlCommand(insertSql, myConnection);
                            long HinhThucQuangCao = getHinhThucQuangCao(row.HinhThucQuangCao);
                            myCommand.Parameters.AddWithValue("@HinhThucQuangCaoREF", HinhThucQuangCao);

                            long SanPham = getSanPham(row.TenSanPham);
                            myCommand.Parameters.AddWithValue("@SanPhamREF", SanPham);

                            long DonVi = getDonVi(row.DonViTinh);
                            myCommand.Parameters.AddWithValue("@DonViTinhREF", DonVi);

                            long Tag = getTag(row.Tag);
                            myCommand.Parameters.AddWithValue("@TagREF", Tag);

                            long Website = getWebsite(row.Website);
                            myCommand.Parameters.AddWithValue("@WebsiteREF", Website);

                            long ChuyenMuc = getChuyenMuc(row.ChuyenMuc);
                            myCommand.Parameters.AddWithValue("@ChuyenMucREF", ChuyenMuc);

                            long Banner = getBanner(row.Banner);
                            myCommand.Parameters.AddWithValue("@BannerREF", Banner);
                            myCommand.Parameters.AddWithValue("@DonGia", row.DonGia.ChangeType<float>());
                            //myCommand.Parameters.AddWithValue("@ThoiGianHieuLucBatDau", row.ThoiGianHieuLucBatDau.ChangeType<DateTime>());
                            //myCommand.Parameters.AddWithValue("@ThoiGianHieuLucKetThuc", row.ThoiGianHieuLucKetThuc.ChangeType<DateTime>());
                            myCommand.Parameters.AddWithValue("@ThoiGianHieuLucBatDau", DateTime.Now);
                            myCommand.Parameters.AddWithValue("@ThoiGianHieuLucKetThuc", DateTime.Now);
                            myCommand.ExecuteNonQuery();
                        }
                    }
                    catch (Exception ex)
                    {
                        CreateLogFiles Err = new CreateLogFiles();
                        Err.ErrorLog(ConfigurationManager.AppSettings["ErrorPath"], "Line" + count + " : " + ex.Message);
                    }
                }
                myConnection.Close();
            }

        }

        public long getHinhThucQuangCao(string Name)
        {
            long ID = 0;

            string connect = ConfigurationManager.ConnectionStrings["QLHDConn"].ConnectionString;
            using (SqlConnection conn = new SqlConnection(connect))
            {
                string queryDetail = @"SELECT * FROM HinhThucQuangCao WHERE TenHinhThucQuangCao =N'" + Name + "' ";

                using (SqlCommand cmd = new SqlCommand(queryDetail, conn))
                {
                    cmd.CommandType = CommandType.Text;
                    conn.Open();

                    using (SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            ID = Convert.ToInt64(rdr["HinhThucQuangCaoID"].ToString());
                        }
                    }
                }
            }

            return ID;
        }

        public long getTag(string Name)
        {
            long ID = 0;

            string connect = ConfigurationManager.ConnectionStrings["QLHDConn"].ConnectionString;
            using (SqlConnection conn = new SqlConnection(connect))
            {
                string queryDetail = @"SELECT * FROM Tag WHERE TenTag =N'" + Name + "' ";

                using (SqlCommand cmd = new SqlCommand(queryDetail, conn))
                {
                    cmd.CommandType = CommandType.Text;
                    conn.Open();

                    using (SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            ID = Convert.ToInt64(rdr["TagID"].ToString());
                        }
                    }
                }
            }

            return ID;
        }

        public long getSanPham(string Name)
        {
            long ID = 0;

            string connect = ConfigurationManager.ConnectionStrings["QLHDConn"].ConnectionString;
            using (SqlConnection conn = new SqlConnection(connect))
            {
                string queryDetail = @"SELECT * FROM SanPham WHERE TenSanPham =N'" + Name + "' ";

                using (SqlCommand cmd = new SqlCommand(queryDetail, conn))
                {
                    cmd.CommandType = CommandType.Text;
                    conn.Open();

                    using (SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            ID = Convert.ToInt64(rdr["SanPhamID"].ToString());
                        }
                    }
                }
            }

            return ID;
        }

        public long getDonVi(string Name)
        {
            long ID = 0;

            string connect = ConfigurationManager.ConnectionStrings["QLHDConn"].ConnectionString;
            using (SqlConnection conn = new SqlConnection(connect))
            {
                string queryDetail = @"SELECT * FROM DonViTinh WHERE TenDonViTinh =N'" + Name + "' ";

                using (SqlCommand cmd = new SqlCommand(queryDetail, conn))
                {
                    cmd.CommandType = CommandType.Text;
                    conn.Open();

                    using (SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            ID = Convert.ToInt64(rdr["DonViTinhID"].ToString());
                        }
                    }
                }
            }

            return ID;
        }

        public long getWebsite(string Name)
        {
            long ID = 0;

            string connect = ConfigurationManager.ConnectionStrings["QLHDConn"].ConnectionString;
            using (SqlConnection conn = new SqlConnection(connect))
            {
                string queryDetail = @"SELECT * FROM WebSite WHERE TenWebSite =N'" + Name + "' ";

                using (SqlCommand cmd = new SqlCommand(queryDetail, conn))
                {
                    cmd.CommandType = CommandType.Text;
                    conn.Open();

                    using (SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            ID = Convert.ToInt64(rdr["WebSiteID"].ToString());
                        }
                    }
                }
            }

            return ID;
        }

        public long getChuyenMuc(string Name)
        {
            long ID = 0;

            string connect = ConfigurationManager.ConnectionStrings["QLHDConn"].ConnectionString;
            using (SqlConnection conn = new SqlConnection(connect))
            {
                string queryDetail = @"SELECT * FROM ChuyenMuc WHERE TenChuyenMuc =N'" + Name + "' ";

                using (SqlCommand cmd = new SqlCommand(queryDetail, conn))
                {
                    cmd.CommandType = CommandType.Text;
                    conn.Open();

                    using (SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            ID = Convert.ToInt64(rdr["ChuyenMucID"].ToString());
                        }
                    }
                }
            }

            return ID;
        }

        public long getBanner(string Name)
        {
            long ID = 0;

            string connect = ConfigurationManager.ConnectionStrings["QLHDConn"].ConnectionString;
            using (SqlConnection conn = new SqlConnection(connect))
            {
                string queryDetail = @"SELECT * FROM Banner WHERE TenBanner =N'" + Name + "' ";

                using (SqlCommand cmd = new SqlCommand(queryDetail, conn))
                {
                    cmd.CommandType = CommandType.Text;
                    conn.Open();

                    using (SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            ID = Convert.ToInt64(rdr["BannerID"].ToString());
                        }
                    }
                }
            }

            return ID;
        }

    }
}
